package com.qymerge.manage.core.utils;

import org.apache.commons.lang.ArrayUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/** */

/**
 * <li>所需Jar包列表</li>
 * <li>poi-3.6-20091214.jar</li>
 * <li>poi-contrib-3.6-20091214.jar</li>
 * <li>poi-examples-3.6-20091214.jar</li>
 * <li>poi-ooxml-3.6-20091214.jar</li>
 * <li>poi-ooxml-schemas-3.6-20091214.jar</li>
 * <li>poi-scratchpad-3.6-20091214.jar</li>
 * <li>xmlbeans-2.3.0.jar</li>
 * <li>geronimo-stax-api_1.0_spec-1.0.jar</li>
 * <ul>
 *
 * @version 1.0
 */
public class ReadExcelBudgetUtil {

    //构造方法
    public ReadExcelBudgetUtil() {
    }

    //总行数
    private int totalRows = 0;

    //总列数
    private int totalCells = 0;


    public int getTotalRows() {
        return totalRows;
    }

    public int getTotalCells() {
        return totalCells;
    }

    /**
     * <ul>
     * <li>Description:[根据文件名读取excel文件]</li>
     *
     * @param fileName 文件名
     * @param startRow 从第几行开始读取（第一行1）
     * @return
     * @throws Exception
     */
    public Map<String, String[][]> readExcel(String fileName, int startRow, int sheetNum) throws IOException {
        Map<String, String[][]> datas = null;

        //检查文件名是否为空或者是否是Excel格式的文件
        if (fileName == null || !fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
            return datas;
        }

        //检查文件是否存在
        File file = new File(fileName);
        if (file == null || !file.exists()) {
            return datas;
        }

        try {
            //调用本类提供的根据流读取的方法
            datas = readByStream(new FileInputStream(file), startRow, sheetNum, fileName);
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        //返回最后读取的结果
        return datas;
    }

    /**
     * <ul>
     * <li>Description:[根据流读取Excel文件]</li>
     *
     * @param inputStream
     * @param startRow    从第几行开始读取数据
     * @param sheetNum    控制读取的sheet页数量
     * @param fileName
     * @return
     */
    public Map<String, String[][]> readByStream(InputStream inputStream, int startRow, int sheetNum, String fileName) {
        Map<String, String[][]> datas = null;
        boolean isExcel2003 = true;
        // 对文件的合法性进行验证
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        try {
            //根据版本选择创建Workbook的方式
            Workbook wb = isExcel2003 ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);
            datas = read(wb, startRow, sheetNum);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return datas;
    }

    private Map<String, String[][]> read(Workbook wb, int startRow, int sheetNum) {
        Map<String, String[][]> dataMap = new HashMap<String, String[][]>();
        startRow = startRow - 1;

        // 得到shell个数
        int sheetNumber = wb.getNumberOfSheets();

        for (int i = 0; i < sheetNum; i++) {
            Sheet sheet = wb.getSheetAt(i);
            String sheetName = sheet.getSheetName();

            this.totalRows = sheet.getPhysicalNumberOfRows(); // 总行数
            if (this.totalRows >= 1 && sheet.getRow(startRow) != null) {
                this.totalCells = sheet.getRow(startRow).getPhysicalNumberOfCells();
            }

            // 分配字符串数组空间[行][列]
            String[][] data = new String[this.totalRows - startRow][this.getTotalCells()];
            // 循环Excel的行
            for (int r = 0; r < this.totalRows - startRow; r++) {
                Row row = sheet.getRow(r + startRow);
                if (row == null) {
                    continue;
                }

                //循环Excel的列
                for (short c = 0; c < this.getTotalCells(); c++) {
                    Cell cell = row.getCell(c);
                    String cellValue = "";
                    if (cell == null) {
                        data[r][c] = cellValue;
                        continue;
                    }
                    //处理数字型的,自动去零
                    if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                        //在excel里,日期也是数字,在此要进行判断
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            SimpleDateFormat simpleDateFormate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            cellValue = simpleDateFormate.format(cell.getDateCellValue());
                        } else {
                            cellValue = getRightStr(cell.getNumericCellValue() + "");
                        }
                    }
                    //处理字符串型
                    else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                        cellValue = cell.getStringCellValue();
                    }
                    //处理布尔型
                    else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
                        cellValue = cell.getBooleanCellValue() + "";
                    }
                    //其它的,非以上几种数据类型
                    else {
                        cellValue = cell.toString() + "";
                    }
                    data[r][c] = cellValue;
                }
            }

            if (sheetName.contains("销量")) {
                // 当前sheet读取所有数据到二位数据组后，添加当前记录到List集合中
                dataMap.put("sales_datas", data);
            } else if (sheetName.contains("收入")) {
                // 当前sheet读取所有数据到二位数据组后，添加当前记录到List集合中
                dataMap.put("income_datas", data);
            } else if (sheetName.contains("成本")) {
                dataMap.put("cost_datas", data);
            }

            if (sheetName.contains("总账")) {
                dataMap.put("general_ledger_datas", data);
            }

        }
        return dataMap;
    }

    /**
     * <ul>
     * <li>Description:[正确地处理整数后自动加零的情况]</li>
     *
     * @param sNum
     * @return
     */
    private String getRightStr(String sNum) {
        if (new Double(sNum).equals(new Double(0)))
            return "0";
        DecimalFormat decimalFormat = new DecimalFormat("#.000000");
        String resultStr = decimalFormat.format(new Double(sNum));
        if (resultStr.matches("^[-+]?\\d+\\.[0]+$")) {
            resultStr = resultStr.substring(0, resultStr.indexOf("."));
        }
        return resultStr;
    }

    /**
     * <ul>
     * <li>Description:[测试main方法]</li>
     *
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
//		List<String[][]> datas = new ReadExcelUtil().read("E:/JQueryTest/document_table_column.xlsx");
//		String[][] data = datas.get(0);
//		for (int i = 0; i < data.length; i++) {
//			System.out.println(data[i][0]+" "+data[i][1]+" "+data[i][2]+" "+data[i][3]+" "+data[i][4]);
//		}

        //Map<String, String[][]> dataMap = new ReadExcelBudgetUtil().readExcel("/Users/dawn/Desktop/SouProjects/IdeaProjects/qingdao-qymerge-manage/src/main/webapp/web/file/gl-20250603.xlsx", 5, 1);
        Map<String, String[][]> dataMap = new ReadExcelBudgetUtil().readExcel("/Users/dawn/Desktop/SouProjects/IdeaProjects/qingdao-qymerge-manage/src/main/webapp/web/file/product-20250603.xlsx", 5, 3);
        for (String key : dataMap.keySet()) {
            String[][] data = dataMap.get(key);
            for (int i = 0; i < data.length; i++) {

                for (int i1 = 0; i1 < data[i].length; i1++) {
                    System.out.print(data[i][i1] + " \t ");
                }

                System.out.println();
            }
        }
    }


}
